
from sqlmodel import SQLModel, Field, create_engine, Session, select
from sqlalchemy import text
from typing import Type, List, Optional
import time
from conf import operationConfig

database = None

class Detection(SQLModel, table=True):
    id: int = Field(primary_key=True, default=None)
    result: str
    confidence: float
    timestamp: str

class SqlHistory(SQLModel, table=True):
    id: int = Field(primary_key=True, default=None)
    sql: str
    timestamp: str

class Database:
    def __init__(self) -> None:
        opc = operationConfig.OperationConfig()
        # mysql_url = f"mysql+pymysql://{opc.get_section_mysql("username")}:{opc.get_section_mysql("password")}@{opc.get_section_mysql("host")}:{opc.get_section_mysql("port")}/{opc.get_section_mysql("database")}"
        # print(mysql_url)
        # self.engine = create_engine(mysql_url)
        self.engine = create_engine("sqlite:///database.db")
        SQLModel.metadata.create_all(self.engine)

    def __get_session(self):
        return Session(self.engine)

    def insert(self, instances: List[SQLModel]):
        with self.__get_session() as session:
            session.add_all(instances)
            session.commit()

    def query(
        self,
        model_class: Type[SQLModel],
        filters: Optional[dict],
        limit: Optional[int],
        offset: Optional[int]
    ):
        with self.__get_session() as session:
            statement = select(model_class)
            if filters:
                for key, value in filters.items():
                    statement = statement.where(getattr(model_class, key) == value)
            if limit:
                statement = statement.limit(limit)
            if offset:
                statement = statement.offset(offset)
            return session.exec(statement).all()

    def execute_sql(self, sql: str, params: Optional[dict]):
        with self.__get_session() as session:
            reuslt = session.execute(text(sql), params or {})
            return reuslt.scalars().all()

    @classmethod
    def get_instance(cls):
        global database
        if database is not None:
            return database
        database = Database()
        return database